const { querySql } = require('../db');
const { jsonSql } = require('../utils');

function getAll() {
    // let sql = `select * from books`;
    let sql = jsonSql.build({
        type: 'select',
        fields: ['*'],
        table: 'books',
    }).query;
    return querySql(sql);
}

function getPage(pageIndex, pageSize, orderBy, filters) {
    // let sql = `select * from books where id > 0 and author = 'zhangsan' order by id asc limit 10;`;
    let sql = jsonSql.build({
        type: 'select',
        fields: ['*'],
        table: 'books',
        condition: [
            // { id: { $gt: (pageIndex - 1) * pageSize } },
            filters
        ],
        sort: orderBy,
        offset: parseInt((pageIndex - 1) * pageSize),
        limit: parseInt(pageSize)
    });
    return querySql(sql.query);
}

function getSearch(filters, orderBy, top) {
    // let sql = select * from books where author = 'zhangsan' and name = 'ssl' order by id asc limit 10;
    let sql = jsonSql.build({
        type: 'select',
        fields: ['*'],
        table: 'books',
        condition: filters,
        sort: orderBy,
        limit: top
    });
    return querySql(sql.query);
}

function getByID(id) {
    let sql = `select * from books where id='${id}'`;
    return querySql(sql);
}

function getCount() {
    let sql = `select count(*) from books`;
    return querySql(sql);
}

function insert(datajson) {
    // datajson = > { a:5, b:text1 }
    // let sql = insert into "table" ("a", "b") values (5, text1);
    let sql = jsonSql.build({
        type: 'insert',
        table: 'books',
        values: datajson
    });
    return querySql(sql.query);
}

function batchInsert(dataset) {
    // dataset = > [{ a:5, b:text1 },{a: 6, b: 'text2'}]
    // let sql = insert into "table" ("a", "b") values (5, text1), (6, text2);
    let sql = jsonSql.build({
        type: 'insert',
        table: 'books',
        values: dataset
    });
    return querySql(sql.query);
}

function update(data, id) {
    // data = > { a:7, b:text3 }
    // let sql = insert into "table" ("a", "b") values (5, text1), (6, text2);
    let sql = jsonSql.build({
        type: 'update',
        table: 'books',
        modifier: data,
        condition: {
            id: parseInt(id)
        }
    });
    return querySql(sql.query);
}

function del(data, id) {
    // let sql = update books set isDelete = 1 where id = 1;
    let sql = jsonSql.build({
        type: 'update',
        table: 'books',
        modifier: {
            isDelete: 1,
            ...data
        },
        condition: {
            id: parseInt(id)
        }
    });
    return querySql(sql.query);
}

function batchDel(data, ids) {
    // let sql = update books set isDelete = 1 where id in (1, 2, 3, 4, 5);
    let sql = jsonSql.build({
        type: 'update',
        table: 'books',
        modifier: {
            isDelete: 1,
            ...data
        },
        condition: {
            id: { $in: ids }
        }
    });
    return querySql(sql.query);
}

function unDel(data, id) {
    // let sql = update books set isDelete = 0 where id = 1;
    let sql = jsonSql.build({
        type: 'update',
        table: 'books',
        modifier: {
            isDelete: 0,
            ...data
        },
        condition: {
            id: parseInt(id)
        }
    });
    return querySql(sql.query);
}

function batchUnDel(data, ids) {
    // let sql = update books set isDelete = 0 where id in (1, 2, 3, 4, 5);
    let sql = jsonSql.build({
        type: 'update',
        table: 'books',
        modifier: {
            isDelete: 0,
            ...data
        },
        condition: {
            id: { $in: ids }
        }
    });
    return querySql(sql.query);
}

function absDel(id) {
    // let sql = delete from books where id = 1;
    let sql = jsonSql.build({
        type: 'remove',
        table: 'books',
        condition: {
            id: parseInt(id)
        }
    });
    return querySql(sql.query);
}

function batchAbsDel(ids) {
    // let sql = delete from books where id in (1, 2, 3, 4, 5);
    let sql = jsonSql.build({
        type: 'remove',
        table: 'books',
        condition: {
            id: { $in: ids }
        }
    });
    return querySql(sql.query);
}

module.exports = {
    getAll, getPage, getSearch, getByID, getCount,
    batchInsert, insert, update,
    del, batchDel, unDel, batchUnDel,
    absDel, batchAbsDel
}